package DBLayer;
import ModelLayer.Product;
import java.sql.*;
import java.util.ArrayList;

public class DBProduct {
    // <editor-fold defaultstate="collapsed" desc="Attributes & Constructors">
    private Connection conn;

    public DBProduct() {
        conn = SQLConnector.getInstance().getConnection();
    }
    // </editor-fold>

    // <editor-fold defaultstate="collapsed" desc="SQL SELECT Methods">
    public Product getProductByID(int id) {
        return selectSingle("SELECT `id_product`, `id_supplier`, `id_manufacturer`, `id_tax`, `id_category_default`, `id_color_default`, `on_sale`, `ean13`, `ecotax`, `quantity`, `price`, `wholesale_price`, `reduction_price`, `reduction_percent`, UNIX_TIMESTAMP(`reduction_from`) AS `reduction_from`, UNIX_TIMESTAMP(`reduction_to`) AS `reduction_to`, `reference`, `supplier_reference`, `location`, `weight`, `out_of_stock`, `quantity_discount`, `customizable`, `uploadable_files`, `text_fields`, `active`, `indexed`, UNIX_TIMESTAMP(`date_add`) AS `date_add`, UNIX_TIMESTAMP(`date_upd`) AS `date_upd`, `description`, `description_short`, `link_rewrite`, `meta_description`, `meta_keywords`, `meta_title`, `name`, `available_now`, `available_later` FROM `ps_product` LEFT JOIN `ps_product_lang` USING (`id_product`) WHERE (`id_lang` = " + Constants.DEFAULT_LANGUAGE + " OR `id_lang` IS NULL) AND `id_product` = " + id + " LIMIT 0, 1;");
    }

    public Product getProductByEan13(String ean13) {
        return selectSingle("SELECT `id_product`, `id_supplier`, `id_manufacturer`, `id_tax`, `id_category_default`, `id_color_default`, `on_sale`, `ean13`, `ecotax`, `quantity`, `price`, `wholesale_price`, `reduction_price`, `reduction_percent`, UNIX_TIMESTAMP(`reduction_from`) AS `reduction_from`, UNIX_TIMESTAMP(`reduction_to`) AS `reduction_to`, `reference`, `supplier_reference`, `location`, `weight`, `out_of_stock`, `quantity_discount`, `customizable`, `uploadable_files`, `text_fields`, `active`, `indexed`, UNIX_TIMESTAMP(`date_add`) AS `date_add`, UNIX_TIMESTAMP(`date_upd`) AS `date_upd`, `description`, `description_short`, `link_rewrite`, `meta_description`, `meta_keywords`, `meta_title`, `name`, `available_now`, `available_later` FROM `ps_product` LEFT JOIN `ps_product_lang` USING (`id_product`) WHERE (`id_lang` = " + Constants.DEFAULT_LANGUAGE + " OR `id_lang` IS NULL) AND `ean13` LIKE '" + ean13 + "' LIMIT 0, 1;");
    }

    public ArrayList<Product> getProductList() {
        return selectMultiple("SELECT `id_product`, `id_supplier`, `id_manufacturer`, `id_tax`, `id_category_default`, `id_color_default`, `on_sale`, `ean13`, `ecotax`, `quantity`, `price`, `wholesale_price`, `reduction_price`, `reduction_percent`, UNIX_TIMESTAMP(`reduction_from`) AS `reduction_from`, UNIX_TIMESTAMP(`reduction_to`) AS `reduction_to`, `reference`, `supplier_reference`, `location`, `weight`, `out_of_stock`, `quantity_discount`, `customizable`, `uploadable_files`, `text_fields`, `active`, `indexed`, UNIX_TIMESTAMP(`date_add`) AS `date_add`, UNIX_TIMESTAMP(`date_upd`) AS `date_upd`, `description`, `description_short`, `link_rewrite`, `meta_description`, `meta_keywords`, `meta_title`, `name`, `available_now`, `available_later` FROM `ps_product` LEFT JOIN `ps_product_lang` USING (`id_product`) WHERE `id_lang` = " + Constants.DEFAULT_LANGUAGE + " OR `id_lang` IS NULL ORDER BY `id_product` ASC;");
    }

    public ArrayList<Integer> getProductIDsByCategoryID(int id) {
        return selectIDs("SELECT `id_product` FROM `ps_category_product` WHERE `id_category` = " + id + " ORDER BY `position` ASC;");
    }

    private Product selectSingle(String sqlStatement) {
        Statement stmt = null;
        ResultSet result = null;
        Product product = new Product();

        try {
            stmt = conn.createStatement();
            result = stmt.executeQuery(sqlStatement);

            if (result.next()) {
                product = buildProduct(result);
            }
            System.out.println("Fetching Product Object");
        } catch (SQLException e) {
            System.out.println("SQLException: " + e.getMessage());
            System.out.println("SQLState: " + e.getSQLState());
            System.out.println("VendorError: " + e.getErrorCode());
        } finally {
            if (result != null) {
                try {
                    result.close();
                } catch (SQLException e) {
                    System.out.println("SQLException: " + e.getMessage());
                    System.out.println("SQLState: " + e.getSQLState());
                    System.out.println("VendorError: " + e.getErrorCode());
                }
                result = null;
            }
            if (stmt != null) {
                try {
                    stmt.close();
                } catch (SQLException e) {
                    System.out.println("SQLException: " + e.getMessage());
                    System.out.println("SQLState: " + e.getSQLState());
                    System.out.println("VendorError: " + e.getErrorCode());
                }
                stmt = null;
            }
        }
        return product;
    }

    private ArrayList<Product> selectMultiple(String sqlStatement) {
        Statement stmt = null;
        ResultSet result = null;
        ArrayList<Product> products = new ArrayList<Product>();

        try {
            stmt = conn.createStatement();
            result = stmt.executeQuery(sqlStatement);

            while (result.next()) {
                Product product = new Product();
                product = buildProduct(result);
                products.add(product);
            }
            System.out.println("Fetching Product List");
        } catch (SQLException e) {
            System.out.println("SQLException: " + e.getMessage());
            System.out.println("SQLState: " + e.getSQLState());
            System.out.println("VendorError: " + e.getErrorCode());
        } finally {
            if (result != null) {
                try {
                    result.close();
                } catch (SQLException e) {
                    System.out.println("SQLException: " + e.getMessage());
                    System.out.println("SQLState: " + e.getSQLState());
                    System.out.println("VendorError: " + e.getErrorCode());
                }
                result = null;
            }
            if (stmt != null) {
                try {
                    stmt.close();
                } catch (SQLException e) {
                    System.out.println("SQLException: " + e.getMessage());
                    System.out.println("SQLState: " + e.getSQLState());
                    System.out.println("VendorError: " + e.getErrorCode());
                }
                stmt = null;
            }
        }
        return products;
    }

    private ArrayList<Integer> selectIDs(String sqlStatement) {
        Statement stmt = null;
        ResultSet result = null;
        ArrayList<Integer> ids = new ArrayList<Integer>();

        try {
            stmt = conn.createStatement();
            result = stmt.executeQuery(sqlStatement);

            while (result.next()) {
                ids.add(result.getInt("id_product"));
            }
            System.out.println("Fetching Product ID List");
        } catch (SQLException e) {
            System.out.println("SQLException: " + e.getMessage());
            System.out.println("SQLState: " + e.getSQLState());
            System.out.println("VendorError: " + e.getErrorCode());
        } finally {
            if (result != null) {
                try {
                    result.close();
                } catch (SQLException e) {
                    System.out.println("SQLException: " + e.getMessage());
                    System.out.println("SQLState: " + e.getSQLState());
                    System.out.println("VendorError: " + e.getErrorCode());
                }
                result = null;
            }
            if (stmt != null) {
                try {
                    stmt.close();
                } catch (SQLException e) {
                    System.out.println("SQLException: " + e.getMessage());
                    System.out.println("SQLState: " + e.getSQLState());
                    System.out.println("VendorError: " + e.getErrorCode());
                }
                stmt = null;
            }
        }
        return ids;
    }

    private Product buildProduct(ResultSet result) {
        DBCategory dbCategory = new DBCategory();
        Product product = new Product();
        try {
            product.setIDProduct(result.getInt("id_product"));
            product.setIDSupplier(result.getInt("id_supplier"));
            product.setIDManufacturer(result.getInt("id_manufacturer"));
            product.setIDTax(result.getInt("id_tax"));
            product.setIDCategoryDefault(result.getInt("id_category_default"));
            product.setIDColorDefault(result.getInt("id_color_default"));
            if (result.getInt("on_sale") == 1) {
                product.setOnSale(true);
            } else {
                product.setOnSale(false);
            }
            product.setEan13(result.getString("ean13"));
            product.setEcotax(result.getFloat("ecotax"));
            product.setQuantity(result.getInt("quantity"));
            product.setPrice(result.getFloat("price"));
            product.setWholesalePrice(result.getFloat("wholesale_price"));
            product.setReductionPrice(result.getFloat("reduction_price"));
            product.setReductionPercent(result.getFloat("reduction_percent"));
            product.setReductionFrom(result.getInt("reduction_from"));
            product.setReductionTo(result.getInt("reduction_to"));
            product.setReference(result.getString("reference"));
            product.setSupplierReference(result.getString("supplier_reference"));
            product.setLocation(result.getString("location"));
            product.setWeight(result.getFloat("weight"));
            product.setOutOfStock(result.getInt("out_of_stock"));
            product.setQuantityDiscount(result.getInt("quantity_discount"));
            product.setCustomizable(result.getInt("customizable"));
            product.setUploadableFiles(result.getInt("uploadable_files"));
            product.setTextFields(result.getInt("text_fields"));
            if (result.getInt("active") == 1) {
                product.setActive(true);
            } else {
                product.setActive(false);
            }
            if (result.getInt("indexed") == 1) {
                product.setIndexed(true);
            } else {
                product.setIndexed(false);
            }
            product.setDateAdd(result.getInt("date_add"));
            product.setDateUpd(result.getInt("date_upd"));
            product.setDescription(result.getString("description"));
            product.setDescriptionShort(result.getString("description_short"));
            product.setLinkRewrite(result.getString("link_rewrite"));
            product.setMetaDescription(result.getString("meta_description"));
            product.setMetaKeywords(result.getString("meta_keywords"));
            product.setMetaTitle(result.getString("meta_title"));
            product.setName(result.getString("name"));
            product.setAvailableNow(result.getString("available_now"));
            product.setAvailableLater(result.getString("available_later"));
            product.setCategoryIDs(dbCategory.getCategoryIDsByProductID(result.getInt("id_product")));

        } catch (Exception e) {
            System.out.println("Exception: " + e.getMessage());
        }
        return product;
    }
    // </editor-fold>

    // <editor-fold defaultstate="collapsed" desc="SQL INSERT, UPDATE & DELETE Methods">
    public int addProduct(Product product) {
        int returnID = 0;
        Statement stmt = null;
        ResultSet result = null;

        String query = "INSERT INTO `ps_product_lang` (`id_product`, `description`, `description_short`, `link_rewrite`, `meta_description`, `meta_keywords`, `meta_title`, `name`, `available_now`, `available_later`) "
                + "VALUES (" + product.getIDProduct() + ", '" + product.getDescription() + "', '" + product.getDescriptionShort() + "', '" + product.getLinkRewrite() + "', '" + product.getMetaDescription() + "', '" + product.getMetaKeywords() + "', '" + product.getMetaTitle() + "', '" + product.getName() + "', '" + product.getAvailableNow() + "', '" + product.getAvailableLater() + "');";

        query += "INSERT INTO `ps_product` (`id_product`, `id_supplier`, `id_manufacturer`, `id_tax`, `id_category_default`, `id_color_default`, `on_sale`, `ean13`, `ecotax`, `quantity`, `price`, `wholesale_price`, `reduction_price`, `reduction_percent`, `reduction_from`, `reduction_to`, `reference`, `supplier_reference`, `location`, `weight`, `out_of_stock`, `quantity_discount`, `customizable`, `uploadable_files`, `text_fields`, `active`, `indexed`, `date_add`, `date_upd`) "
                + "VALUES (" + product.getIDProduct() + ", " + product.getIDSupplier() + ", " + product.getIDManufacturer() + ", " + product.getIDCategoryDefault() + ", " + product.getIDColorDefault() + ", ";
        if (product.getOnSale() == true)        { query += "1, "; }
        else                                    { query += "0, "; }
        query += "'" + product.getEan13() + "', " + product.getEcotax() + ", " + product.getQuantity() + ", " + product.getPrice() + ", " + product.getWholesalePrice() + ", " + product.getReductionPrice() + ", " + product.getReductionPercent() + ", FROM_UNIXTIME(" + product.getReductionFrom() + "), FROM_UNIXTIME" + product.getReductionTo() + "), '" + product.getReference() + "', '" + product.getSupplierReference() + "', '" + product.getLocation() + "', " + product.getWeight() + ", " + product.getOutOfStock() + ", " + product.getQuantityDiscount() + ", " + product.getCustomizable() + ", " + product.getUploadableFiles() + ", " + product.getTextFields() + ", ";
        if (product.getActive() == true)        { query += "1, "; }
        else                                    { query += "0, "; }
        if (product.getIndexed() == true)       { query += "1, "; }
        else                                    { query += "0, "; }
        query += "FROM_UNIXTIME(" + product.getDateAdd() + "), FROM_UNIXTIME(" + product.getDateUpd() + "));";
        try {
            stmt = conn.createStatement();
            stmt.executeUpdate(query, Statement.RETURN_GENERATED_KEYS);
            result = stmt.getGeneratedKeys();
            if (result.next()) {
                returnID = result.getInt(1);
            }
            System.out.println("Added Product ID " + returnID);
        } catch (SQLException e) {
            System.out.println("SQLException: " + e.getMessage());
            System.out.println("SQLState: " + e.getSQLState());
            System.out.println("VendorError: " + e.getErrorCode());
        } finally {
            if (result != null) {
                try {
                    result.close();
                } catch (SQLException e) {
                    System.out.println("SQLException: " + e.getMessage());
                    System.out.println("SQLState: " + e.getSQLState());
                    System.out.println("VendorError: " + e.getErrorCode());
                }
                result = null;
            }
            if (stmt != null) {
                try {
                    stmt.close();
                } catch (SQLException e) {
                    System.out.println("SQLException: " + e.getMessage());
                    System.out.println("SQLState: " + e.getSQLState());
                    System.out.println("VendorError: " + e.getErrorCode());
                }
                stmt = null;
            }
        }
        return returnID;
    }

    public int updateProduct(Product product, int id) {
        int returnID = 0;
        Statement stmt = null;
	String query = "UPDATE `ps_product` SET "
                + "`id_product`             = "  + product.getIDProduct() + ", "
                + "`id_supplier`            = "  + product.getIDSupplier() + ", "
                + "`id_manufacturer`        = "  + product.getIDManufacturer() + ", "
                + "`id_tax`                 = "  + product.getIDTax() + ", "
                + "`id_category_default`    = "  + product.getIDCategoryDefault() + ", "
                + "`id_color_default`       = "  + product.getIDColorDefault() + ", ";
        if (product.getOnSale() == true)    { query += "`on_sale` = 1 "; }
        else                                { query += "`on_sale` = 0 "; }
        query +=  "`ean13`                  = '"  + product.getEan13() + "', "
                + "`ecotax`                 = "  + product.getEcotax() + ", "
                + "`quantity`               = "  + product.getQuantity() + ", "
                + "`price`                  = "  + product.getPrice() + ", "
                + "`wholesale_price`        = "  + product.getWholesalePrice() + ", "
                + "`reduction_price`        = "  + product.getReductionPrice() + ", "
                + "`reduction_percent`      = "  + product.getReductionPercent() + ", "
                + "`reduction_from`         = FROM_UNIXTIME("  + product.getReductionFrom() + "), "
                + "`reduction_to`           = FROM_UNIXTIME("  + product.getReductionTo() + "), "
                + "`reference`              = '" + product.getReference() + "', "
                + "`supplier_reference`     = '" + product.getSupplierReference() + "', "
                + "`location`               = '" + product.getLocation() + "', "
                + "`weight`                 = "  + product.getWeight() + ", "
                + "`out_of_stock`           = "  + product.getOutOfStock() + ", "
                + "`quantity_discount`      = "  + product.getQuantityDiscount() + ", "
                + "`customizable`           = "  + product.getCustomizable() + ", "
                + "`uploadable_files`       = "  + product.getUploadableFiles() + ", "
                + "`text_fields`            = "  + product.getTextFields() + ", ";
        if (product.getActive() == true)    { query += "`active` = 1 "; }
        else                                { query += "`active` = 0 "; }
        if (product.getIndexed() == true)   { query += "`indexed` = 1 "; }
        else                                { query += "`indexed` = 0 "; }
        query +=  "`date_add`               = FROM_UNIXTIME("  + product.getDateAdd() + "), "
                + "`date_upd`               = FROM_UNIXTIME("  + product.getDateUpd() + "), "
                + "WHERE `id_product` = " + id + " LIMIT 1; ";

        query += "UPDATE `ps_product_lang` SET "
                + "`id_product`             = "  + product.getIDProduct() + ", "
                + "`description`            = '" + product.getDescription() + "', "
                + "`description_short`      = '" + product.getDescriptionShort() + "', "
                + "`link_rewrite`           = '" + product.getLinkRewrite() + "', "
                + "`meta_description`       = '" + product.getMetaDescription() + "', "
                + "`meta_keywords`          = '" + product.getMetaKeywords() + "', "
                + "`meta_title`             = '" + product.getMetaTitle() + "', "
                + "`name`                   = '" + product.getName() + "', "
                + "`available_now`          = '" + product.getAvailableNow() + "', "
                + "`available_later`        = '" + product.getAvailableLater() + "' "
                + "WHERE `id_lang` = " + Constants.DEFAULT_LANGUAGE + " AND `id_product` = " + id + " LIMIT 1;";

        try {
            stmt = conn.createStatement();
            stmt.executeUpdate(query);
            returnID = product.getIDProduct();
            System.out.println("Updated Product ID " + id);
        } catch (SQLException e) {
            System.out.println("SQLException: " + e.getMessage());
            System.out.println("SQLState: " + e.getSQLState());
            System.out.println("VendorError: " + e.getErrorCode());
        } finally {
            if (stmt != null) {
                try {
                    stmt.close();
                } catch (SQLException e) {
                    System.out.println("SQLException: " + e.getMessage());
                    System.out.println("SQLState: " + e.getSQLState());
                    System.out.println("VendorError: " + e.getErrorCode());
                }
                stmt = null;
            }
        }
        return returnID;
    }

    public void deleteProduct(int id) {
        Statement stmt = null;

        try {
            stmt = conn.createStatement();
            stmt.executeUpdate("DELETE FROM `ps_product` WHERE `id_product` = " + id + " LIMIT 1;");
            System.out.println("Deleted Product ID " + id);
        } catch (SQLException e) {
            System.out.println("SQLException: " + e.getMessage());
            System.out.println("SQLState: " + e.getSQLState());
            System.out.println("VendorError: " + e.getErrorCode());
        } finally {
            if (stmt != null) {
                try {
                    stmt.close();
                } catch (SQLException e) {
                    System.out.println("SQLException: " + e.getMessage());
                    System.out.println("SQLState: " + e.getSQLState());
                    System.out.println("VendorError: " + e.getErrorCode());
                }
                stmt = null;
            }
        }
    }
    // </editor-fold>
}
